import math
from typing import List

from database.mysqlhelper import MySqLHelper

db = MySqLHelper()

insert_sql = 'insert into t_stock_day_info (f_code,f_name,f_rq,f_main_i,f_main_o,f_main_io,f_sb_i,f_sb_o,f_sb_io,f_b_i,f_b_o,f_b_io,' \
             'f_m_i,f_m_o,f_m_io,f_s_i,f_s_o,f_s_io,f_turnover_rate,f_market_value,f_circulation_value,f_platform) ' \
             'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) '
select_sql = 'select f_code,f_name,f_rq,f_main_i,f_main_o, f_main_io,f_sb_i,f_sb_o,f_sb_io,f_b_i,f_b_o,f_b_io,' \
             'f_m_i,f_m_o,f_m_io,f_s_i,f_s_o,f_s_io,f_turnover_rate,f_market_value,f_circulation_value,f_platform,f_update_time ' \
             'from t_stock_day_info '
sql_1 = 'select f_rq, cast(sum(!!!!!) as signed) from t_stock_day_info where f_rq >= @@@@@ group by f_rq order by f_rq '
sql_2 = 'select f_rq, cast(sum(!!!!!) as signed) from t_stock_day_info where f_rq >= @@@@@ and f_code in (#####) group by f_rq order by f_rq '


class StockDayInfoDto:
    def __init__(self, code, name, rq, main_i, main_o, main_io, sb_i, sb_o, sb_io, b_i, b_o, b_io, m_i, m_o, m_io, s_i,
                 s_o, s_io, hsl, market_val, cir_val, platform):
        self.code = code
        self.name = name
        self.rq = rq
        self.main_i = main_i
        self.main_o = main_o
        self.main_io = main_io
        self.sb_i = sb_i
        self.sb_o = sb_o
        self.sb_io = sb_io
        self.b_i = b_i
        self.b_o = b_o
        self.b_io = b_io
        self.m_i = m_i
        self.m_o = m_o
        self.m_io = m_io
        self.s_i = s_i
        self.s_o = s_o
        self.s_io = s_io
        self.hsl = hsl
        self.market_val = market_val
        self.cir_val = cir_val
        self.platform = platform


def format_info(res):
    if not res:
        return
    return StockDayInfoDto(str(res[0], encoding="utf-8"), str(res[1], encoding="utf-8"), res[2], int(res[3]),
                           int(res[4]), int(res[5]), int(res[6]), int(res[7]), int(res[8]), int(res[9]),
                           int(res[10]), int(res[11]), int(res[12]), int(res[13]), int(res[14]), int(res[15]),
                           int(res[16]), int(res[17]), float(res[18]), int(res[19]), int(res[20]),
                           str(res[21], encoding="utf-8"))


def format_list(res):
    list = []
    for p in res:
        list.append(format_info(p))
    return list


def select_by_codes(codes: list) -> List[StockDayInfoDto]:
    if len(codes) == 0:
        return
    sql = select_sql + 'where f_code in ('
    for code in codes:
        sql += '\'' + code + '\','
    return format_list(db.selectall(sql[:-1] + ')'))


# 获取最新的n条
def select_new_n(code, size):
    sql1 = select_sql + ' where f_code = %s order by f_rq desc limit %s '
    return format_list(db.selectall(sql1, (code, size)))


# 添加一条
# data {}
def insert(data):
    return db.insertone(insert_sql, to_tuples(data))


# 添加多条
# data = []
def insert_multiple(data_list):
    d_len = len(data_list)
    if d_len == 0:
        return 0
    # 增加多条
    c_len = math.ceil(d_len / 100)
    count = 0
    for i in range(c_len):
        li = []
        start = min(i * 100, d_len)
        end = min(start + 100, d_len)
        if start == end:
            break
        for data in data_list[start:end]:
            li.append(to_tuples(data))
        count += db.insertmany(insert_sql, li)
    return count


# f_code,f_name,f_rq,f_main_i,f_main_o,f_main_io,f_sb_i,f_sb_o,f_sb_io,f_b_i,f_b_o,f_b_io,' \
# 'f_m_i,f_m_o,f_m_io,f_s_i,f_s_o,f_s_io,f_turnover_rate,f_market_value,f_circulation_value,f_platform
# 转换为元组
def to_tuples(info):
    return (info['code'], info['name'], info['rq'], info['main_i'], info['main_o'], info['main_io'],
            info['sb_i'], info['sb_o'], info['sb_io'], info['b_i'], info['b_o'], info['b_io'],
            info['m_i'], info['m_o'], info['m_io'], info['s_i'], info['s_o'], info['s_io'],
            info['turnover_rate'], info['market_value'], info['circulation_value'], info['platform'])


# 根据code和日期更新
def update_code_rq(info):
    sql = 'update t_stock_day_info set  f_main_i = %s, f_main_o = %s, f_main_io = %s, ' \
          'f_sb_i = %s, f_sb_o = %s, f_sb_io = %s, f_b_i = %s, f_b_o = %s, f_b_io = %s, ' \
          'f_m_i = %s, f_m_o = %s, f_m_io = %s, f_s_i = %s, f_s_o = %s, f_s_io = %s, ' \
          'f_turnover_rate = %s, f_market_value = %s, f_circulation_value = %s ' \
          'where f_code = %s and f_rq = %s '
    return db.update(sql, (info['main_i'], info['main_o'], info['main_io'],
                           info['sb_i'], info['sb_o'], info['sb_io'], info['b_i'], info['b_o'], info['b_io'],
                           info['m_i'], info['m_o'], info['m_io'], info['s_i'], info['s_o'], info['s_io'],
                           info['turnover_rate'], info['market_value'], info['circulation_value'],
                           info['code'], info['rq']))


# 获取最新的一条
def select(code, rq):
    sql1 = select_sql + ' where f_code = %s and f_rq = %s '
    return format_one(db.selectone(sql1, (code, rq)))


# 日期从大到小
def select_all_desc(code):
    sql1 = select_sql + ' where f_code = %s order by f_rq desc '
    return format_res(db.selectall(sql1, code))


# 日期从小到大
def select_all_asc(code):
    sql1 = select_sql + ' where f_code = %s order by f_rq asc '
    return format_res(db.selectall(sql1, code))


# 获取最新的n条
def select_size(code, size):
    sql1 = select_sql + ' where f_code = %s order by f_rq desc limit %s '
    return format_res(db.selectall(sql1, (code, size)))


# 获取区间值
def select_interval(code, beg_rq, end_rq):
    sql1 = select_sql + 'where f_code = %s and f_rq between %s and %s order by f_rq desc '
    return format_res(db.selectall(sql1, (code, beg_rq, end_rq)))


# 从日期获取
def select_from_asc(code, beg_rq):
    sql1 = select_sql + 'where f_code = %s and f_rq >= %s order by f_rq '
    return format_res(db.selectall(sql1, (code, beg_rq)))


# 格式化结果
def format_one(res):
    if not res:
        return
    return {'code': str(res[0], encoding="utf-8"), 'name': str(res[1], encoding="utf-8"), 'rq': res[2],
            'main_i': int(res[3]), 'main_o': int(res[4]), 'main_io': int(res[5]),
            'sb_i': int(res[6]), 'sb_o': int(res[7]), 'sb_io': int(res[8]),
            'b_i': int(res[9]), 'b_o': int(res[10]), 'b_io': int(res[11]),
            'm_i': int(res[12]), 'm_o': int(res[13]), 'm_io': int(res[14]),
            's_i': int(res[15]), 's_o': int(res[16]), 's_io': int(res[17]),
            'turnover_rate': float(res[18]), 'market_value': int(res[19]), 'circulation_value': int(res[20]),
            'platform': str(res[21], encoding="utf-8")}


def select_sum_io_rq(column_name: str, beg_rq: str):
    res = db.selectall(sql_1.replace('!!!!!', column_name).replace('@@@@@', "'" + beg_rq + "'"))
    if not res:
        return
    list = []
    for r in res:
        list.append({'rq': r[0], 'sum': int(r[1])})
    return list


def select_sum_io_rq_codes(column_name: str, beg_rq: str, codes: list):
    if len(codes) == 0:
        return None
    code_str = ''
    for code in codes:
        code_str += code + ", "
    res = db.selectall(sql_1.replace('!!!!!', column_name)
                       .replace('@@@@@', "'" + beg_rq + "'")
                       .replace('#####', code_str[:-2]))
    if not res:
        return
    list = []
    for r in res:
        list.append({'rq': r[0], 'sum': int(r[1])})
    return list


# 格式化结果
def format_res(res):
    if not res:
        return []
    list = []
    for p in res:
        list.append(format_one(p))
    return list
